package ldh.im.fxbase.dao;

import ldh.im.dto.enums.MessageType;
import ldh.im.fxbase.data.pojo.ImHotMessageTotal;
import ldh.im.fxbase.data.pojo.ImMessage;
import ldh.im.fxbase.data.pojo.ImSession;
import ldh.im.fxbase.data.pojo.status.ImMessageExt;
import ldh.im.fxbase.data.pojo.status.ImMessageActionStatus;
import ldh.im.fxbase.data.pojo.status.ImMessageFileStatus;
import ldh.im.fxbase.data.pojo.status.ImMessageStatus;
import ldh.im.fxbase.data.pojo.ImMessageActionData;
import ldh.im.fxbase.util.BeanFactory;
import ldh.im.util.DateUtil;
import ldh.im.fxbase.util.DbUtil;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringEscapeUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

public class ImMessageDao extends BaseDao {

    private static final Logger LOGGER = LoggerFactory.getLogger(ImMessageDao.class);

    private static final String TABLE_NAME = "im_message";
    private final static String SELECT_COLUMNS = "id, clientId, currentUserId, serverId, fromUserId, toUserId, messageType, content, groupCode, sessionId, ext, ext1, ext2, " +
            "refType refTypeValue, refMessageIds, status statusValue, fileStatus fileStatusValue, actionStatus actionStatusValue, serverReceiveTime, createTime, updateTime";

    public List<ImMessage> getBySessionId(Long sessionId, int size) throws SQLException {
        return getBySessionIdAndDate(sessionId, null, size);
    }

    public List<ImMessage> getBySessionIdAndDate(Long sessionId, Date serverReceiveDate, int size) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);
        ImSession session = sessionDao.getSessionById(sessionId);
        return DbUtil.transaction((queryRunner, connection) -> {
            List<ImMessageExt> list = null;
            String where = serverReceiveDate != null ? String.format("and serverReceiveTime >= '%s'", DateUtil.format(serverReceiveDate, "yyyy-MM-dd HH:mm:ss.S")) : "";
            String sql = String.format("select %s from im_message where sessionId = ? and status in (1, 3, 5, 11) %s order by serverReceiveTime desc limit 0, %s", SELECT_COLUMNS, where, size);
            list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), sessionId);
            List<ImMessage> messages = convertToMessages(list, true);
            loadRefMessages(session.getFromUserId(), messages);
            return messages;
        });
    }

    public List<ImMessage> getNewMessagesBySessionId(Long sessionId, Date serverReceiveDate, int size) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);
        ImSession session = sessionDao.getSessionById(sessionId);
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = serverReceiveDate != null ? String.format("and serverReceiveTime >= '%s'", DateUtil.format(serverReceiveDate, "yyyy-MM-dd HH:mm:ss.S")) : "";
            // 不加载这些消息：消息被删除，消息被撤回，消息被引用，服务器已屏蔽的消息
            String sql = String.format("select %s from im_message where sessionId = ? and status in (1, 3, 5, 11) %s order by serverReceiveTime limit 0, %s", SELECT_COLUMNS, where, size);
            LOGGER.info("get new messages sql {}", sql);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), sessionId);
            List<ImMessage> messages =  convertToMessages(list, true);
            loadRefMessages(session.getFromUserId(), messages);
            return messages;
        });
    }

    public List<ImMessage> getOldMessagesBySessionId(Long sessionId, Date serverReceiveDate, int size) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);
        ImSession session = sessionDao.getSessionById(sessionId);
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = serverReceiveDate != null ? String.format("and serverReceiveTime <= '%s'", DateUtil.format(serverReceiveDate, "yyyy-MM-dd HH:mm:ss.S")) : "";
            String sql = String.format("select %s from im_message where sessionId = ?  and status in (1, 3, 5, 11) %s order by serverReceiveTime desc limit 0, %s", SELECT_COLUMNS, where, size);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), sessionId);
            List<ImMessage> messages =  convertToMessages(list, true);
            loadRefMessages(session.getFromUserId(), messages);
            return messages;
        });
    }

    public List<ImMessage> getMessagesBySessionId(Long sessionId, String searchText, List<MessageType> messageTypes, int pageIndex, int pageSize) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);
        ImSession session = sessionDao.getSessionById(sessionId);
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = sessionId != null ? String.format(" and sessionId = %s ", sessionId) : "";
            where += messageTypes != null && messageTypes.size() > 0 ? String.format(" and messageType in ('%s') ", messageTypes.stream().map(m->m.name()).collect(Collectors.joining(","))) : "";
            where += searchText != null && !searchText.trim().equals("") ? String.format(" and content like ?") : "";
            String sql = String.format("select %s from im_message where 1=1 %s and status not in (9,11,12) order by serverReceiveTime desc limit %s, %s", SELECT_COLUMNS, where,
                    pageIndex * pageSize, pageSize);

            LOGGER.debug("getMessagesBySessionId sql: {}", sql);
            List<ImMessageExt> list = null;
            if (searchText != null && !searchText.trim().equals("")) {
                list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), "%" + searchText + "%");
            } else {
                list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class));
            }
            List<ImMessage> messages =  convertToMessages(list, false);
            loadRefMessages(session.getFromUserId(), messages);
            return messages;
        });
    }

    /**
     * 搜索
     * @param sessionId
     * @param search
     * @param startDate
     * @param size
     * @param isDesc true 向后搜索。 false： 向前搜索
     * @return
     * @throws SQLException
     */
    public List<ImMessage> searchBySessionId(Long sessionId, String search, Date startDate, int size, boolean isDesc) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);
        ImSession session = sessionDao.getSessionById(sessionId);
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = sessionId != null ? String.format(" and sessionId = %s ", sessionId) : "";
            if (startDate != null && isDesc) where += String.format(" and serverReceiveTime < '%s' ", DateUtil.format(startDate, "yyyy-MM-dd HH:mm:ss.S"));
            if (startDate != null && !isDesc) where += String.format(" and serverReceiveTime > '%s' ", DateUtil.format(startDate, "yyyy-MM-dd HH:mm:ss.S"));
            String order = isDesc ? "order by serverId desc" : "order by serverId";
            String sql = String.format("select %s from im_message where content like ? %s and status not in (9,11,12) %s limit 0, %s", SELECT_COLUMNS, where, order, size);
            LOGGER.debug("getMessagesBySessionId sql: {}", sql);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), "%" + search + "%");
            List<ImMessage> messages =  convertToMessages(list, isDesc);
            loadRefMessages(session.getFromUserId(), messages);
            return messages;
        });
    }

    public List<ImMessage> getMessagesBySessionId(Long sessionId, Date startDate, List<MessageType> messageTypes, int size, boolean isDesc) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);
        ImSession session = sessionDao.getSessionById(sessionId);
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = sessionId != null ? String.format("and sessionId = %s", sessionId) : "";
            where += messageTypes != null && messageTypes.size() > 0 ? String.format(" and messageType in ('%s') ", messageTypes.stream().map(m->m.name()).collect(Collectors.joining(","))) : "";
            if (startDate != null && isDesc) where += String.format(" and serverReceiveTime < '%s' ", DateUtil.format(startDate, "yyyy-MM-dd HH:mm:ss.S"));
            if (startDate != null && !isDesc) where += String.format(" and serverReceiveTime > '%s' ", DateUtil.format(startDate, "yyyy-MM-dd HH:mm:ss.S"));
            String order = isDesc ? "order by serverId desc" : "order by serverId";
            String sql = String.format("select %s from im_message where 1=1 %s and status not in (9,11,12) %s limit 0, %s", SELECT_COLUMNS, where, order, size);
            LOGGER.debug("getMessagesBySessionId sql: {}", sql);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class));
            List<ImMessage> messages =  convertToMessages(list, isDesc);
            loadRefMessages(session.getFromUserId(), messages);
            return messages;
        });
    }

    public Long getMessageTotal(Long sessionId, String searchText, List<MessageType> messageTypes) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = sessionId != null ? String.format("and sessionId = %s", sessionId) : "";
            where += messageTypes != null && messageTypes.size() > 0 ? String.format("and messageType in ('%s')", messageTypes.stream().map(m->m.name()).collect(Collectors.joining(","))) : "";
            where += searchText != null && !searchText.trim().equals("") ? String.format("and content like ?") : "";
            String sql = String.format("select count(1) from im_message where 1=1 %s and status not in (9,11,12)", where);
            long total = 0L;
            if (searchText != null && !searchText.trim().equals("")) {
                total = queryRunner.query(connection, sql, new ScalarHandler<Long>(), "%" + searchText + "%");
            } else {
                total = queryRunner.query(connection, sql, new ScalarHandler<Long>());
            }
            return total;
        });
    }

    /**
     * 获取未发送成功的消息
     * @param userId
     * @param size
     * @return
     * @throws SQLException
     */
    public List<ImMessage> getNeedSendMessagesByUserId(String userId, int size) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_message where currentUserId = ? and fromUserId = ? and status = 1 " +
                    "order by id asc limit 0, %s", SELECT_COLUMNS, size);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), userId, userId);
            return convertToMessages(list, false);
        });
    }

    public List<ImMessage> getByServerIdsAndUserId(Set<String> serverIds, String userId) throws SQLException {
        if (serverIds.size() < 1) return new ArrayList<>();
        return DbUtil.transaction((queryRunner, connection) -> {
            String serverIdStr = serverIds.stream().map(id-> "\'" + id + "\'").collect(Collectors.joining(","));
            String sql = String.format("select %s from im_message where currentUserId = ? and serverId in (%s)", SELECT_COLUMNS, serverIdStr);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), userId);
            return convertToMessages(list, false);
        });
    }

    public ImMessage getById(Long id) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_message where id = ?", SELECT_COLUMNS);
            ImMessageExt imMessageExt = queryRunner.query(connection, sql, new BeanHandler<>(ImMessageExt.class), id);
            return imMessageExt;
        });
    }

    /**
     * 获取未发送成功的附件的消息
     * @param userId
     * @param size
     * @return
     * @throws SQLException
     */
    public List<ImMessage> getNeedSendFileMessagesByUserId(String userId, int size) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_message where currentUserId = ? and fromUserId = ? and status not in (11, 12) and " +
                    "(fileStatus = 1 or (fileStatus is null and messageType in ('Image', 'File', 'Stream', 'Audio') and status in (3, 4))) " +
                    "order by id limit 0, %s", SELECT_COLUMNS, size);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), userId, userId);
            return convertToMessages(list, false);
        });
    }

    /**
     * 获取消息事件同步未成功的消息, 索引利用率低，性能低下
     * @param userId
     * @param size
     * @return
     * @throws SQLException
     */
    public List<ImMessage> getNeedSynActionStatusMessagesByUserId(String userId, int size) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_message where currentUserId = ? and actionStatus in (1, 6) and status not in (3,9,12,30) " +
                    "order by id limit 0, %s", SELECT_COLUMNS, size);
            List<ImMessageExt> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), userId);
            return convertToMessages(list, true);
        });
    }

    public List<ImHotMessageTotal> getHotMessageTotal(String userId, LocalDateTime startDate) throws SQLException {
        String sql = String.format("select m.sessionId sessionId, count(*) total from im_message m where m.currentUserId = ? and " +
                "m.status in (1, 3) and serverReceiveTime > ? and m.actionStatus in(0) and " +
                "((m.toUserId = ? and m.fromUserId != ?) or (m.groupCode is not null and m.fromUserId != ?)) group by m.sessionId");

        return DbUtil.transaction((queryRunner, connection) -> {
            List<ImHotMessageTotal> list = queryRunner.query(connection, sql, new BeanListHandler<>(ImHotMessageTotal.class),
                    userId, DateUtil.format(startDate, "yyyy-MM-dd HH:mm:ss"), userId, userId, userId);
            return list;
        });
    }

    private  List<ImMessage> convertToMessages(List<ImMessageExt> imMessageExtList, boolean isDesc) {
        handleHtmlContent(imMessageExtList);
        List<ImMessage> imMessages = imMessageExtList.stream().map(m->m).collect(Collectors.toList());
        if (isDesc) {
            Collections.reverse(imMessages);
        }
        return imMessages;
    }

    private void handleHtmlContent(List<ImMessageExt> list) {
        list.forEach(m -> {
            if (m.getMessageType() == MessageType.Html) {
                m.setContent(StringEscapeUtils.unescapeHtml4(m.getContent()));
            }
            m.initMessageExt();
        });
    }

    private String handleHtmlContent(ImMessage message) {
        if (message.getMessageType() == MessageType.Html) {
            return StringEscapeUtils.escapeHtml4(message.getContent());
        }
        return message.getContent();
    }

    public ImMessage getMessageByServerId(String currentUserId, String serverId) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_message where currentUserId = ? and (clientId = ? or serverId = ?) and status not in (9,11,12)", SELECT_COLUMNS);
            ImMessageExt imMessage = queryRunner.query(connection, sql, new BeanHandler<>(ImMessageExt.class), currentUserId, serverId, serverId);
            if (imMessage != null) {
                loadRefMessages(currentUserId, Arrays.asList(imMessage));
            }
            return imMessage;
        });
    }

    public List<ImMessage> getMessageByServerIds(String currentUserId, List<String> serverIds) throws SQLException {
        String serverIdsPara = serverIds.stream().map(s-> "\'" + s + "\'").collect(Collectors.joining(","));
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_message where currentUserId = ? and (clientId in (%s) or serverId in (%s) ) and status not in (9,11,12)", SELECT_COLUMNS,
                    serverIdsPara, serverIdsPara);
            List<ImMessageExt> imMessages = queryRunner.query(connection, sql, new BeanListHandler<>(ImMessageExt.class), currentUserId);
            return imMessages.stream().map(m->m).collect(Collectors.toList());
        });
    }

    public void saveMessage(ImMessage message) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            ResultSetHandler<Long> h = new ScalarHandler();
            Byte fileStatus = valueEnum(message.getFileStatus());
            Byte actionStatus = valueEnum(message.getFileStatus());
            String sql = String.format("insert into im_message(clientId, currentUserId, fromUserId, toUserId, serverId, messageType, content, groupCode, sessionId, ext, ext1, ext2, status, " +
                    "fileStatus, actionStatus, serverReceiveTime, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            Long n = queryRunner.insert(connection, sql, h, message.getClientId(), message.getCurrentUserId(), message.getFromUserId(), message.getToUserId(), message.getServerId(),
                    message.getMessageType().name(), handleHtmlContent(message), message.getImSession().getGroupCode(), message.getImSession().getId(),
                    message.getExt(), message.getExt1(), message.getExt2(), valueEnum(message.getStatus()), fileStatus, actionStatus, message.getServerReceiveTime(),
                    message.getCreateDateTime(), message.getCreateDateTime());
            message.setId(n);

            return n;
        });
    }

    public void saveMessages(List<ImMessage> messages) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            ArrayListHandler h = new ArrayListHandler();
            Object[][] paras = messages.stream().map(message-> new Object[] {message.getClientId(), message.getCurrentUserId(), message.getFromUserId(), message.getToUserId(), message.getServerId(),
                    message.getMessageType().name(), handleHtmlContent(message), message.getGroupCode(),
                    message.getImSession() != null ? message.getImSession().getId() : null, valueEnum(message.getRefType()), message.getRefMessageIds(), message.getExt(),
                    valueEnum(message.getStatus()),
                    valueEnum(message.getFileStatus()), valueEnum(message.getActionStatus()),
                    message.getServerReceiveTime(), message.getCreateDateTime(), message.getCreateDateTime()}).toArray(Object[][]::new);
            String sql = String.format("insert into im_message(clientId, currentUserId, fromUserId, toUserId, serverId, messageType, content, groupCode, sessionId, refType, refMessageIds, ext, " +
                    "status, fileStatus, actionStatus, serverReceiveTime, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            List<Object[]> ids = queryRunner.insertBatch(connection, sql, h, paras);
            for (int i=0; i<messages.size(); i++) {
                messages.get(i).setId((Long)ids.get(i)[0]);
            }

            return ids;
        });
    }

    public void batchUpdateMessages(List<ImMessage> messages) throws SQLException {
        if (messages.size() < 1) return;
        DbUtil.transaction((queryRunner, connection) -> {
            Object[][] paras = messages.stream().map(message-> new Object[] {message.getFromUserId(), message.getToUserId(),
                    message.getMessageType().name(), handleHtmlContent(message),
                    message.getImSession().getGroupCode(), message.getImSession().getId(), message.getExt(), 
                    valueEnum(message.getStatus()), valueEnum(message.getFileStatus()), valueEnum(message.getActionStatus()),
                    message.getServerReceiveTime(), message.getUpdateDateTime(), message.getCurrentUserId(), message.getServerId()}).toArray(Object[][]::new);
            String sql = String.format("update im_message set fromUserId = ?, toUserId = ?, messageType = ?, content = ?, groupCode = ?, " +
                    "sessionId =?, ext = ?,  status = ?, fileStatus = ?, actionStatus = ?, serverReceiveTime = ?,  updateTime=? where currentUserId = ? and serverId = ?");
            int[] ns = batchUpdates(queryRunner, connection, sql, paras);

            return ns.length;
        });
    }

    public void batchUpdateMessageExts(List<ImMessage> messages) throws SQLException {
        if (messages.size() < 1) return;
        DbUtil.transaction((queryRunner, connection) -> {
            Object[][] paras = messages.stream().map(message-> new Object[] {message.getExt(), message.getCurrentUserId(), message.getServerId()}).toArray(Object[][]::new);
            String sql = String.format("update im_message set ext = ? where currentUserId = ? and serverId = ?");
            int[] ns = batchUpdates(queryRunner, connection, sql, paras);
            return ns.length;
        });
    }

    public void update(ImMessage message) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            ArrayListHandler h = new ArrayListHandler();
            String sql = String.format("update im_message set  content = ?,  ext = ?, ext1 = ?, ext2 =?,  status = ?, fileStatus = ?, actionStatus = ?, serverReceiveTime = ?,  updateTime=? where " +
                    " id = ?");
            int ns = queryRunner.update(connection, sql, handleHtmlContent(message), message.getExt(), message.getExt1(), message.getExt2(),
                    valueEnum(message.getStatus()), valueEnum(message.getFileStatus()), valueEnum(message.getActionStatus()),
                    message.getServerReceiveTime(), message.getUpdateDateTime(), message.getId());
            if (ns != 1) {
                throw new RuntimeException("更新im_message失败");
            }

            return ns;
        });
    }

    public void batchDeleteMessages(List<ImMessage> messages) throws SQLException {
        if (messages.size() < 1) return;
        DbUtil.transaction((queryRunner, connection) -> {
            Object[][] paras = messages.stream().map(message-> new Object[] {message.getCurrentUserId(), message.getServerId()}).toArray(Object[][]::new);
            String sql = String.format("update im_message set status = 9 where currentUserId = ? and serverId = ?");
            int[] ns = queryRunner.batch(connection, sql, paras);
            if (ns.length != messages.size()) {
                throw new RuntimeException("更新im_group失败， n =" + ns.length);
            }

            return ns.length;
        });
    }

    public void cleanMessages(String userId, List<Long> messageIds) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            String messageIdPara = messageIds.stream().map(m->m.toString()).collect(Collectors.joining(","));
            String sql = String.format("update im_message set status = ? where currentUserId = ? and id in (%s)", messageIdPara);
            int ns = queryRunner.execute(connection, sql, ImMessageStatus.delete.getValue(), userId);
            
            return ns;
        });
    }

    public void deleteMessages(String userId, List<Long> messageIds) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            String messageIdPara = messageIds.stream().map(m->m.toString()).collect(Collectors.joining(","));
            String sql = String.format("delete im_message where currentUserId = ? and id in (%s)", messageIdPara);
            int ns = queryRunner.execute(connection, sql, userId);

            return ns;
        });
    }

    public void batchChangeMessageStatus(List<ImMessage> messages) throws SQLException {
        Object[][] paras = messages.stream().map(m-> new Object[] {m.getServerId(), valueEnum(m.getStatus()), m.getServerReceiveTime(), new Date(),
                m.getId()}).toArray(Object[][]::new);
        DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("update im_message set serverId = ?,  status = ?, serverReceiveTime= ?, updateTime=? where id = ?");
            int[] ns = queryRunner.batch(connection, sql, paras);
            return ns.length;
        });
    }

    /**
     * 更新附件状态
     * @param id
     * @param imMessageFileStatus
     * @throws SQLException
     */
    public void changeMessageFileStatus(Long id,  ImMessageFileStatus imMessageFileStatus) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("update im_message set fileStatus = ?, updateTime=? where id = ?");
            updateBeanById(queryRunner, connection, sql, valueEnum(imMessageFileStatus), LocalDateTime.now(), id);
            return null;
        });
    }

    /**
     * 更新附件状态
     * @param id
     * @param imMessageFileStatus
     * @throws SQLException
     */
    public void changeMessageFileStatus(Long id,  String content, String ext, ImMessageFileStatus imMessageFileStatus) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("update im_message set fileStatus = ?, content=?, ext = ?, updateTime=? where id = ?");

            updateBeanById(queryRunner, connection, sql, valueEnum(imMessageFileStatus), content, ext, LocalDateTime.now(), id);
            return null;
        });
    }

    /**
     * 更新消息事件同步状态
     * @param ids
     * @param messageStatus
     * @throws SQLException
     */
    public void changeMessageActionStatus(Set<Long> ids, ImMessageActionStatus messageStatus) throws SQLException {
        if (ids.size() < 1) return;
        DbUtil.transaction((queryRunner, connection) -> {
            String idStrs = ids.stream().map(id->id.toString()).collect(Collectors.joining(","));
            String sql = String.format("update im_message set actionStatus = ?, updateTime=? where id in (%s)", idStrs);
            LOGGER.info("changeMessageActionStatus SQL {}", sql);
            int n = queryRunner.execute(connection, sql, valueEnum(messageStatus), LocalDateTime.now());
            if (n != ids.size()) {
                throw new RuntimeException("更新im_message失败， n =" + n);
            }
            return null;
        });
    }

    public void changeMessageActionStatus(List<ImMessageActionData> actionVoList) throws SQLException {
        if (actionVoList.size() < 1) return;
        DbUtil.transaction((queryRunner, connection) -> {
            Object[][] paras = actionVoList.stream().map(mv-> messageActionVoToUpdateArray(mv)).toArray(Object[][]::new);
            String sql = String.format("update im_message set actionStatus = ?, updateTime=? where id = ?");
            LOGGER.info("changeMessageActionStatus SQL {}", sql);
            int[] ns = queryRunner.batch(connection, sql, paras);
            if (ns.length != actionVoList.size()) {
                throw new RuntimeException("更新im_message失败， n =" + ns.length);
            }
            return null;
        });
    }

    /**
     * 更新消息事件同步状态
     * @param serverIdSet
     * @param messageStatus
     * @throws SQLException
     */
    public void changeMessageActionStatus(String userId, Set<String> serverIdSet, ImMessageActionStatus messageStatus) throws SQLException {
        if (serverIdSet.size() < 1) return;
        DbUtil.transaction((queryRunner, connection) -> {
            String idStrs = serverIdSet.stream().map(id-> "\'" + id + "\'").collect(Collectors.joining(","));
            String sql = String.format("update im_message set actionStatus = ?, updateTime=? where currentUserId = ? and serverId in (%s)", idStrs);
            LOGGER.info("changeMessageActionStatus SQL {}", sql);
            int n = queryRunner.execute(connection, sql, valueEnum(messageStatus), LocalDateTime.now(), userId);
            if (n != serverIdSet.size()) {
                throw new RuntimeException("更新im_message失败， n =" + n);
            }
            return null;
        });
    }

    /**
     * 删除消息
     * @param id
     * @throws SQLException
     */
    public void deleteMessage(Long id) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("update im_message set status = ?, updateTime=? where id = ?");
            updateBeanById(queryRunner, connection, sql, valueEnum(ImMessageStatus.delete), LocalDateTime.now(), id);
            return null;
        });
    }

    public void updateMessageStatus(Long messageId, ImMessageStatus messageStatus, ImMessageActionStatus actionStatus) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("update im_message set status = ?, actionStatus = ?,  updateTime=? where id = ?");

            updateBeanById(queryRunner, connection, sql, valueEnum(messageStatus), valueEnum(actionStatus), LocalDateTime.now(), messageId);
            return null;
        });
    }

    private Object[] messageActionVoToUpdateArray(ImMessageActionData mv) {
        Object[] objArray = new Object[3];
        objArray[0] =valueEnum(mv.getActionStatus());
        objArray[1] = LocalDateTime.now();
        objArray[2] = mv.getMessageId();
        return objArray;
    }

    private void loadRefMessages(String userId, List<ImMessage> messages) throws SQLException {
        ImSessionDao sessionDao = BeanFactory.getInstance().getBean(ImSessionDao.class);

        for (ImMessage message : messages) {
            if (message.isRef()) {
                String messageIds = message.getRefMessageIds();
                String[] messageIdArray = messageIds.split(",");
                List<ImMessage> refMessages = getMessageByServerIds(userId, Arrays.asList(messageIdArray));
                if(message.getMessageType() == MessageType.Chat) {
                    for (ImMessage imMessage : refMessages) {
                        if (imMessage.getSessionId() != null) {
                            ImSession session = sessionDao.getSessionById(imMessage.getSessionId());
                            imMessage.setImSession(session);
                        }
                    }
                }
                message.setRefMessages(refMessages);
            }
        }
    }
}
